package com.fanyouzhao.ehome.dao.impl;

import com.fanyouzhao.ehome.dao.BaseDao;
import com.fanyouzhao.ehome.dao.UserDao;
import com.fanyouzhao.ehome.entity.Cp;
import com.fanyouzhao.ehome.entity.User;
import com.fanyouzhao.ehome.util.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author fanyouzhao
 * @date 2021年4月1日 上午10:28:39
 */
public class UserDaoImpl extends BaseDao<User> implements UserDao {

    // 引入DBUtil
    private DBUtil dbutil = new DBUtil();

    @Override
    public User selectById(Integer id) throws Exception {
        return super.selectById(id, User.class);
    }

    @Override
    public User selectByName(String name) throws Exception {
        return super.selectByName(name, User.class);
    }

    @Override
    public User selectByPhone(String phone) throws Exception {
        return super.selectByPhone(phone, User.class);
    }

    @Override
    public User selectByUsername(String username) throws Exception {
        return super.selectByUsername(username, User.class);
    }

    @Override
    public List<User> selectAll() throws Exception {
        return super.selectAll(User.class);
    }

    @Override
    public List<User> selectLikeNameorPhoneByPage(String searchString, int page, int size) throws Exception {
        return super.selectLikeNameorPhoneByPage(searchString, page, size, User.class);
    }

    @Override
    public Integer getCpn(Integer cpId, String phone) throws Exception {
        String sql = "INSERT INTO tb_get (cp_id,user_phone)\n" +
                "VALUES (?,?);";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, cpId);
        preparedStatement.setObject(2, phone);

        int lines = preparedStatement.executeUpdate();
        dbutil.closeAll();
        return lines;
    }

    @Override
    public List<User> selectByPage(int page, int size) throws Exception {
        return super.selectByPage(page, size, User.class);
    }

    @Override
    public List<Cp> selectCpsNotUsedByPhone(String phone) throws Exception {
        String sql = "SELECT tb_cp.*\n" +
                "FROM tb_get\n" +
                "JOIN tb_cp\n" +
                "ON tb_cp.cp_id=tb_get.cp_id\n" +
                "WHERE user_phone=? AND get_isUsed='0' AND cp_inUse='1'";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<Cp> list = new ArrayList<Cp>();
        Cp cp = null;
        while (resultSet.next()) {
            cp = new Cp();
            cp.setId(resultSet.getInt(1));
            cp.setName(resultSet.getString(2));
            cp.setDesc(resultSet.getString(3));
            cp.setImg(resultSet.getString(4));
            cp.setRole(resultSet.getString(5));
            cp.setCollectExpire(resultSet.getDate(6));
            cp.setUseExpire(resultSet.getDate(7));
            cp.setInUse(resultSet.getBoolean(8));
            list.add(cp);
        }
        dbutil.closeAll();
        return list;
    }

    @Override
    public List<Cp> selectCpsUsedByPhone(String phone) throws Exception {
        String sql = "SELECT tb_cp.*\n" +
                "FROM tb_get\n" +
                "JOIN tb_cp\n" +
                "ON tb_cp.cp_id=tb_get.cp_id\n" +
                "WHERE user_phone=? AND get_isUsed='1' OR cp_inUse='0'";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<Cp> list = new ArrayList<Cp>();
        Cp cp = null;
        while (resultSet.next()) {
            cp = new Cp();
            cp.setId(resultSet.getInt(1));
            cp.setName(resultSet.getString(2));
            cp.setDesc(resultSet.getString(3));
            cp.setImg(resultSet.getString(4));
            cp.setRole(resultSet.getString(5));
            cp.setCollectExpire(resultSet.getDate(6));
            cp.setUseExpire(resultSet.getDate(7));
            cp.setInUse(resultSet.getBoolean(8));
            list.add(cp);
        }
        dbutil.closeAll();
        return list;
    }

    @Override
    public List<Cp> selectNotGetCpsByPhone(String phone) throws Exception {
        //查询某手机号用户未领取的优惠券
        //1.手机号 2.申请有效期前 3.未领取的 4.已上架的
        String sql = "SELECT * FROM tb_cp WHERE cp_id \n" +
                "NOT IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
                "AND cp_collectExpire >= DATE_FORMAT(NOW(), '%Y-%m-%d')\n" +
                "AND cp_inUse = '1'";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<Cp> list = new ArrayList<Cp>();
        Cp cp = null;
        while (resultSet.next()) {
            cp = new Cp();
            cp.setId(resultSet.getInt(1));
            cp.setName(resultSet.getString(2));
            cp.setDesc(resultSet.getString(3));
            cp.setImg(resultSet.getString(4));
            cp.setRole(resultSet.getString(5));
            cp.setCollectExpire(resultSet.getDate(6));
            cp.setUseExpire(resultSet.getDate(7));
            cp.setInUse(resultSet.getBoolean(8));
            list.add(cp);
        }
        dbutil.closeAll();
        return list;
    }

    @Override
    public List<Cp> selectGetCpsByPhone(String phone) throws Exception {
        //查询某手机号用户未领取的优惠券
        //1.手机号 2.使用有效期前 3.已领取的 4.已上架的
        String sql = "SELECT * FROM tb_cp WHERE cp_id\n" +
                "IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
                "AND cp_useExpire >= DATE_FORMAT(NOW(), '%Y-%m-%d')\n" +
                "AND cp_inUse = '1'";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<Cp> list = new ArrayList<Cp>();
        Cp cp = null;
        while (resultSet.next()) {
            cp = new Cp();
            cp.setId(resultSet.getInt(1));
            cp.setName(resultSet.getString(2));
            cp.setDesc(resultSet.getString(3));
            cp.setImg(resultSet.getString(4));
            cp.setRole(resultSet.getString(5));
            cp.setCollectExpire(resultSet.getDate(6));
            cp.setUseExpire(resultSet.getDate(7));
            cp.setInUse(resultSet.getBoolean(8));
            list.add(cp);
        }
        dbutil.closeAll();
        return list;
    }

    @Override
    public List<Cp> selectOverExpireCpsByPhone(String phone) throws Exception {
        //查询某手机号用户已过期的优惠券
        //1.手机号 2.已领取的 3.超过使用有效期的 4.不论是否上架
        String sql = "SELECT * FROM tb_cp WHERE cp_id\n" +
                "IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
                "AND cp_useExpire < DATE_FORMAT(NOW(), '%Y-%m-%d')";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<Cp> list = new ArrayList<Cp>();
        Cp cp = null;
        while (resultSet.next()) {
            cp = new Cp();
            cp.setId(resultSet.getInt(1));
            cp.setName(resultSet.getString(2));
            cp.setDesc(resultSet.getString(3));
            cp.setImg(resultSet.getString(4));
            cp.setRole(resultSet.getString(5));
            cp.setCollectExpire(resultSet.getDate(6));
            cp.setUseExpire(resultSet.getDate(7));
            cp.setInUse(resultSet.getBoolean(8));
            list.add(cp);
        }
        dbutil.closeAll();
        return list;
    }

    @Override
    public Integer getCount() throws Exception {
        return super.getCount(User.class);
    }

    @Override
    public Integer getCountSearch(String searchString) throws Exception {
        return super.getCountSearch(searchString, User.class);
    }

    @Override
    public Integer insert(User user) throws Exception {
        return super.insert(user);
    }

    @Override
    public Integer deleteById(Integer id) throws Exception {
        return super.deleteById(id, User.class);
    }

    @Override
    public Integer deleteByUsername(String username) throws Exception {
        return super.deleteByUsername(username, User.class);
    }

    @Override
    public Integer update(Integer id, User user) throws Exception {
        return super.update(id, user);// 返回影响的行数
    }

}
